import FileSaver from "file-saver";
import * as XLSX from "xlsx";
import XLSXSTYLE from "xlsx-style";
import { Loading, MessageBox } from "element-ui";
import {downloadExcel} from "@/api/excel";

let row_content = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR","AS","AT","AU","AV","AW","AX","AY","AZ"];
let basic_title = ["公共机构基本信息","公共机构能源资源消费状况","公共机构数据中心机房能源消费状况","公共机构采暖能源资源消费状况","公共机构能源资源消费统计分级汇总情况","公共机构能源资源消费统计分类汇总情况","公共机构数据中心机房能源消费统计汇总情况","公共机构采暖能源资源消费统计汇总情况"];
let type_title = ["表    号： 国管节能基1表","表    号： 国管节能基2表","表    号： 国管节能基3表","表    号： 国管节能基4表","表    号： 国管节能综1表","表    号： 国管节能综2表","表    号： 国管节能综3表","表    号： 国管节能综4表"];
let common_title = ["制表机关： 国务院机关事务管理局","批准机关： 国家统计局","批准文号：国统制[2022]130号","有效期至：  2025年9月"];
let merges_start_title = [4,4,4,4,44,8,8,8];//基表标题从N列开始，数组里放置的就是N
let fixed_field = ["单位名称（盖章）：","单位负责人：","填表人：","填报时间："];
let description = ["说明:单位负责人指本单位主管节能工作的领导:统计负责人指本单位负责节能工作的处(科、室)领导，填表人指本单位具体负责填写能耗统计报表的人员。",
                        "说明:单位负责人指本单位主管节能工作的领导:统计负责人指本单位负责节能工作的处(科、室)领导:填表人指本单位具体负责填写能耗统计报表的人员。",
                        "说明:1.本报表由使用数据中心机房的单位，逐一对每个机房的能源消费情况进行调查统计。2.单位负责人指本单位主管节能工作的领导，统计负责人指本单位负责节能工作的处(科、室)领导，填表人指本单位具体负责填写能耗统计报表的人员。",
                        "说明:1.本报表由实施采暖的单位对统计年度结束的采暖周期内的能源消费情况进行调查填写。如:报 2022 年数据应填报2021年冬季至2022年春季采暖周期的相关信息。2，单位负责人指本单位主管节能工作的领导:统计负责人指本单位负责节能工作的处(科、室)领导:填表人指本单位具体负责填写能耗统计报表的人员。",
                        "说明:1.各级系统、行政主管部门填写本表时，应汇总至部门所属三级单位，三级以下单位的数据由所属第三级单位统一汇总填写。\n" +
                        "2.县级以上各级人民政府管理公共机构节能工作的机构填写本表时，所辖乡镇级公共机构统一由县级行政主管部门汇总填写。\n" +
                        "3.单位负责人指本单位主管节能工作的领导;填表人指本单位具体负责填写能耗统计报表的人员。",
                        "说明:1.各级系统、行政主管部门填写本表时，应汇总至部门所属三级单位，三级以下单位的数据由所属第三级单位统一汇总填写。\n" +
                        "2.县级以上各级人民政府管理公共机构节能工作的机构填写本表时，所辖乡镇级公共机构统一由县级行政主管部门汇总填写。\n" +
                        "3.单位负责人指本单位主管节能工作的领导;填表人指本单位具体负责填写能耗统计报表的人员。",
                        "说明:单位负责人指本单位主管节能工作的领导:填表人指本单位具体负责填写能耗统计报表的人员。",
                        "说明:单位负责人指本单位主管节能工作的领导:填表人指本单位具体负责填写能耗统计报表的人员。"];
let null_content = {};
let title_style = {
  alignment: {
    horizontal: 'center',   //水平居中对齐
    vertical: 'center',
  },
  font: {
    // 自定义字体颜色
    color: { rgb: '000000' },
    sz: 14,
    bold: true
  },
  numFmt: 0
}
let title_style_des = {
  alignment: {
    horizontal: false,   //水平
    vertical: false,   //垂直
    wrapText: true     //自动换行
  }
}
let last_style_loss = {
  border: {
    top: { style: 'thin' },
    left: { style: 'thin' },
    bottom: { style: 'thin' },
    right: { style: 'thin' }
  }
}
/**
 * 将表数据导出到 Excel 文件。
 * @param {Object} tableRef - 对表组件的引用
 * @param {string} fileName - Excel 文件的名称。
 */
export function exportExcel(tableRef, fileName, cols = []) {

    return new Promise(function (resolve, reject) {
        MessageBox.confirm("导出 excel, 是否继续?", "提示", {
            confirmButtonText: "确定",
            cancelButtonText: "取消",
            type: "warning",
            closeOnClickModal: false,
        })
            .then(function () {
                const loadingInstance = Loading.service({
                    lock: true,
                    text: "正在导出...",
                    spinner: "el-icon-loading",
                    background: "rgba(0, 0, 0, 0.7)",
                });
                const ws = XLSX.utils.table_to_sheet(tableRef.$el, {
                    raw: true,
                });
                let wb;
                let fix = document.querySelector(".el-table__fixed-right");
                if (fix) {
                    //判断要导出的节点中是否有fixed的表格，如果有，转换excel时先将该dom移除，然后append回去
                    wb = XLSX.utils.book_new(
                        document.querySelector(".el-table").removeChild(fix)
                    );
                    document.querySelector(".el-table").appendChild(fix);
                } else {
                    wb = XLSX.utils.book_new(document.querySelector(".el-table"));
                }
                XLSX.utils.book_append_sheet(wb, ws, fileName);
                setExlStyle(wb['Sheets'][fileName], cols, fix,fileName); // 设置列宽 字号等
                if (wb['Sheets'][fileName]['!merges'] && wb['Sheets'][fileName]['!merges'].length > 0) {
                    addRangeBorder(wb['Sheets'][fileName]['!merges'], wb['Sheets'][fileName]) // 单元格合并
                }
                let wb_out = XLSXSTYLE.write(wb, { type: 'buffer' })
                try {
                    FileSaver.saveAs(
                        new Blob([wb_out], { type: "application/octet-stream" }),
                        fileName + '.xlsx'
                    );
                    resolve();
                } catch (e) {
                    if (typeof console !== "undefined") {
                        console.log(e, wbout);
                    }
                    MessageBox.alert("导出失败", "错误", {
                        type: "error",
                    });
                    reject(e);
                } finally {
                    loadingInstance.close();
                }
            })
            .catch(function () {
                reject();
            });
    });
}

export function exportBasicExcel(tableRef, fileName, cols = [],sn,infos) {

  return new Promise(function (resolve, reject) {
    MessageBox.confirm("导出 excel, 是否继续?", "提示", {
      confirmButtonText: "确定",
      cancelButtonText: "取消",
      type: "warning",
      closeOnClickModal: false,
    })
      .then(function () {
        const loadingInstance = Loading.service({
          lock: true,
          text: "正在导出...",
          spinner: "el-icon-loading",
          background: "rgba(0, 0, 0, 0.7)",
        });
        const ws = XLSX.utils.table_to_sheet(tableRef.$el, {
          raw: true,
        });
        let wb;
        let fix = document.querySelector(".el-table__fixed-right");
        if (fix) {
          //判断要导出的节点中是否有fixed的表格，如果有，转换excel时先将该dom移除，然后append回去
          wb = XLSX.utils.book_new(
            document.querySelector(".el-table").removeChild(fix)
          );
          document.querySelector(".el-table").appendChild(fix);
        } else {
          wb = XLSX.utils.book_new(document.querySelector(".el-table"));
        }
        XLSX.utils.book_append_sheet(wb, ws, fileName);
        setBasicExlStyle(wb['Sheets'][fileName], cols);
        if (wb['Sheets'][fileName]['!merges'] && wb['Sheets'][fileName]['!merges'].length > 0) {
          addRangeBorder(wb['Sheets'][fileName]['!merges'], wb['Sheets'][fileName]) // 单元格合并
        }
        excelRestructuringData(wb['Sheets'][fileName],sn,infos);
        let wb_out = XLSXSTYLE.write(wb, { type: 'buffer' })
        try {
          FileSaver.saveAs(
            new Blob([wb_out], { type: "application/octet-stream" }),
            fileName + '.xlsx'
          );
          resolve();
        } catch (e) {
          if (typeof console !== "undefined") {
            console.log(e, wbout);
          }
          MessageBox.alert("导出失败", "错误", {
            type: "error",
          });
          reject(e);
        } finally {
          loadingInstance.close();
        }
      })
      .catch(function () {
        reject();
      });
  });
}

export function exportBasicBackstageExcel(fileName, deptId) {

    return new Promise(function (resolve, reject) {
        MessageBox.confirm("导出 excel, 是否继续?", "提示", {
            confirmButtonText: "确定",
            cancelButtonText: "取消",
            type: "warning",
            closeOnClickModal: false,
        })
            .then(function () {
                const loadingInstance = Loading.service({
                    lock: true,
                    text: "正在导出...",
                    spinner: "el-icon-loading",
                    background: "rgba(0, 0, 0, 0.7)",
                });
                downloadExcel(deptId).then((res) => {
                    try {
                        FileSaver.saveAs(
                            new Blob([res], { type: "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" }),
                            fileName + '.xlsx'
                        );
                    } catch (e) {
                        MessageBox.alert("导出失败", "错误", {
                            type: "error",
                        });
                        reject(e);
                    } finally {
                        loadingInstance.close();
                    }
                });
            })
            .catch(function () {
                reject();
            });
    });
}

const setExlStyle = function (data, cols, fix,title) {
    console.log(data);
    let resData = deleteItemNameList(data['!fullref'],fix);
    data['!fullref'] = resData[0];
    data['!ref'] = resData[0];
    let borderAll = {  //单元格外侧框线
        top: {
            style: 'thin',
        },
        bottom: {
            style: 'thin'
        },
        left: {
            style: 'thin'
        },
        right: {
            style: 'thin'
        }
    };
    data['!cols'] = [];
    for (let key in data) {
        //有fix场景对数组进行人工剔除重复数据
        if (fix) {
            if (resData.includes(key)) {
                delete data[key];
                continue;
            }
        }
        // 按照数值调整文件字体颜色，数值<0，字体变红。
        let col = '000000'
        if (data[key] instanceof Object) {
            // if (data[key].v < 0) {
            //     col = 'ff0000'
            // } else {
            //     col = '000000'
            // }
            data[key].s = {
                border: borderAll,
                alignment: {
                    horizontal: 'center',   //水平居中对齐
                    vertical: 'center'
                },
                font: {
                    // 自定义字体颜色
                    color: { rgb: col },
                    sz: 11
                },
                bold: true,
                numFmt: 0 // 设置单元格为普通文本格式
            }
            // 自定义列宽，每列都一样
            // data['!cols'].push({wpx: 80})
        }
        if (data[key].v && !isNaN(data[key].v)) {
          data[key].t = 'n'; // 设置单元格值为数字类型
        }
    }
    // 自定义列宽, 每列不一样
    data['!cols'] = cols || [];
    //数据内容下标向下移动，第一行放置标题
    indexDownAddTitle(data,title);
    data['A1'].v = title;
}

const addRangeBorder = function (range, ws) {
    range.forEach(item => {
        let style = {
            s: {
                border: {
                    top: { style: 'thin' },
                    left: { style: 'thin' },
                    bottom: { style: 'thin' },
                    right: { style: 'thin' }
                }
            }
        }
        // 处理合并行
        for (let i = item.s.c; i <= item.e.c; i++) {
            ws[`${row_content[i]}${Number(item.e.r) + 1}`] = ws[`${row_content[i]}${Number(item.e.r) + 1}`] || style
            // 处理合并列
            for (let k = item.s.r + 2; k <= item.e.r + 1; k++) {
                ws[row_content[i] + k] = ws[row_content[k] + item.e.r] || style
            }

        }
    })
    return ws;
}

const deleteItemNameList = function (range,fix) {
    let res = [];
    let strArr = range.split(":")[1];
    let strArray = range.split(":")[1].split("");
    let index;
    for (let i=0;i<strArray.length;i++) {
        if (strArray[i] > 0) {
            index = i;
            break;
        }
    }
    let len = strArr.substr(index);
    let az = strArr.substr(0,index);
    let indexAZ;
    for (let i=0;i<row_content.length;i++) {
        if (row_content[i] == az) {
            indexAZ = i-1;
            break;
        }
    }
    if (fix) {
        res.push(range.split(":")[0] + ":" + row_content[indexAZ-1] + len/2);
        for (let n=len;n>len/2;n--) {
            for (let m=0;m<indexAZ;m++) {
                res.push(row_content[m]+n);
            }
        }
    } else {
        res.push(range.split(":")[0] + ":" + row_content[indexAZ] + (Number(len)+1));
    }
    res.push(az+"1");
    return res;
}

const indexDownAddTitle = function (data,title) {
    let range = data['!ref'];
    let strArr = range.split(":")[1];
    let strBefore = range.split(":")[0];
    let strArray = range.split(":")[1].split("");
    let index;
    for (let i=0;i<strArray.length;i++) {
        if (strArray[i] > 0) {
            index = i;
            break;
        }
    }
    let len = strArr.substr(index);
    let az = strArr.substr(0,index);
    let indexAZ;
    for (let i=0;i<row_content.length;i++) {
        if (row_content[i] == az) {
            indexAZ = i;
            break;
        }
    }
    for (let n=len;n>0;n--) {
        for (let m=0;m<indexAZ+1;m++) {
            data[row_content[m]+(Number(n)+1)] = Object.assign({},data[row_content[m]+n]);
        }
    }
    data['!ref'] = strBefore + ":" + az + (Number(len)+1);
    data['!fullref'] = strBefore + ":" + az + (Number(len)+1);
    let add =   {
        s: { c: 0, r: 0 },
        e: { c: indexAZ, r: 0 },
    }
    let merges = data['!merges'];
    if ( merges && merges.length > 0) {
        for (let i=0;i<merges.length;i++) {
            merges[i].s.r++;
            merges[i].e.r++;
        }
        merges.push(add);
    } else {
        let temp = [];
        temp.push(add);
        data['!merges'] = temp;
    }
    return data;
}

const excelRestructuringData = function (data,sn,unit_info) {
  //common_title+基表+ 总标题
  let move = 1 + 1 + common_title.length;
  let range = data['!ref'];
  let strArr = range.split(":")[1];
  let strBefore = range.split(":")[0];
  let strArray = range.split(":")[1].split("");
  let index;
  for (let i=0;i<strArray.length;i++) {
    if (strArray[i] > 0) {
      index = i;
      break;
    }
  }
  let len = strArr.substring(index);
  let az = strArr.substring(0,index);
  let indexAZ;
  for (let i=0;i<row_content.length;i++) {
    if (row_content[i] == az) {
      indexAZ = i;
      break;
    }
  }
  //下移move长度，用于加标题详细内容
  for (let n=len;n>0;n--) {
    for (let m=0;m<indexAZ+1;m++) {
      data[row_content[m]+(Number(n)+move)] = Object.assign({},data[row_content[m]+n]);
    }
  }
  //计算加上标题后的数据总长度，为加尾部数据做准备
  let add_title_len = Number(len) + move;
  //fullref&ref是导出数据的范围，这里indexAZ-1是为解决多一列的问题，move为下移行数长度用于加标题，+3为尾部数据长度1行单位责任人2行合并说明
  data['!ref'] = strBefore + ":" + row_content[indexAZ-1] + (Number(len)+move+3);
  data['!fullref'] = strBefore + ":" + row_content[indexAZ-1] + (Number(len)+move+3);
  //整理单元格合并情况
  let resMerges = titleMergesCompose(sn,indexAZ-1,move,add_title_len);
  let merges = data['!merges'];
  //原本单元格合并情况，做非空判断处理
  if ( merges && merges.length > 0) {
    for (let i=0;i<merges.length;i++) {
      merges[i].s.r = merges[i].s.r + move;
      merges[i].e.r = merges[i].e.r + move;
    }
    merges = merges.concat(resMerges);
    data['!merges'] = merges;
  } else {
    data['!merges'] = resMerges;
  }
  //右对齐标题从start开始
  let start = row_content[merges_start_title[sn-1]];
  let tv = {};
  //标题加common_title，其他的内容置空
  for (let s=1;s<move+1;s++) {
    for (let mm=0;mm<indexAZ;mm++) {
      if (start != row_content[mm]) {
        data[row_content[mm]+s] = null_content;
      } else {
        if (s <= common_title.length) {
          tv.t = "s";
          tv.v = common_title[s-1];
          data[row_content[mm]+(Number(s)+2)] = Object.assign({},tv);
        }
      }
    }
  }
  //第一行标题样式内容添加
  tv.s = title_style;
  tv.t = "s";
  tv.v = basic_title[sn-1];
  data[row_content[0]+1] = Object.assign({},tv);
  //表号
  tv.s = null_content;
  tv.t = "s";
  tv.v = type_title[sn-1];
  data[row_content[merges_start_title[sn-1]]+2] = Object.assign({},tv);
  //第一行其他数据置空
  data[row_content[merges_start_title[sn-1]]+1] = null_content;
  //单位名称
  tv.v = fixed_field[0] + unit_info[0];
  data[row_content[0]+move] = Object.assign({},tv);
  //报告类型
  tv.v = unit_info[4] + unit_info[5];
  data[row_content[1]+move] = Object.assign({},tv);
  //单位负责人、填表人、填报时间
  tv.v = fixed_field[1] + unit_info[1];
  data[row_content[0]+(Number(add_title_len)+1)] = Object.assign({},tv);
  tv.v = fixed_field[2] + unit_info[2];
  data[row_content[1]+(Number(add_title_len)+1)] = Object.assign({},tv);
  tv.v = fixed_field[3] + unit_info[3];
  data[row_content[3]+(Number(add_title_len)+1)] = Object.assign({},tv);
  //说明栏样式内容
  tv.s = title_style_des;
  tv.v = description[sn-1];
  data[row_content[0]+(Number(add_title_len)+2)] = Object.assign({},tv);
  //常规数据最后一行样式错误，重新加一次样式
  for (let p=0;p<indexAZ;p++) {
    tv.s = last_style_loss;
    tv.v = data[row_content[p]+(Number(add_title_len))].v;
    data[row_content[p]+(Number(add_title_len))] = Object.assign({},tv);
  }
  console.log(data);
  return data;
}

const titleMergesCompose = function (sn,index,move,add_title_len) {
  let merges = [];
  //总标题
  let title =   {
    s: { c: 0, r: 0 },
    e: { c: index, r: 0 },
  }
  merges.push(title);
  //基表综表标题
  for (let i=1;i<move;i++) {
    let temp = {
      s: { c: merges_start_title[sn-1], r: i },
      e: { c: index, r: i }
    }
    merges.push(temp);
  }
  //尾部说明内容
  let description_merges = {
    s: { c: 0, r: add_title_len+1 },
    e: { c: index, r: add_title_len+2 },
  }
  merges.push(description_merges);
  return merges;
}
const setBasicExlStyle = function (data, cols) {
  console.log(data);
  let borderAll = {  //单元格外侧框线
    top: {
      style: 'thin',
    },
    bottom: {
      style: 'thin'
    },
    left: {
      style: 'thin'
    },
    right: {
      style: 'thin'
    }
  };
  data['!cols'] = [];
  for (let key in data) {
    // 按照数值调整文件字体颜色，数值<0，字体变红。
    let col = '000000'
    if (data[key] instanceof Object) {
      data[key].s = {
        border: borderAll,
        alignment: {
          horizontal: 'center',   //水平居中对齐
          vertical: 'center'
        },
        font: {
          // 自定义字体颜色
          color: { rgb: col },
          sz: 11
        },
        bold: true,
        numFmt: 0
      }
    }
  }
  // 自定义列宽, 每列不一样
  data['!cols'] = cols || [];
}
